package com.cc.excell;

import com.cc.dao.DBTransToExcell;
import com.cc.dao.LibaryProperties;
import com.cc.dao.dto.ShowTableDto;
import com.cc.read.CustomReadTable;
import com.cc.read.LibaryReadTable;
import com.cc.utils.excell.DbTransToExcellUtils;
import com.cc.utils.poi.ExcelPoiUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


/**
 * @author cc
 * @date 2022/8/13 13:50
 * @Description : 转换成Excell
 */

public class ToExcell {
  private static String fileAddress = null;

  static {
    Properties properties = new Properties();

    InputStream inputStream = ToExcell.class.getClassLoader().getResourceAsStream("excell.properties");
    try {
      properties.load(inputStream);
      fileAddress = properties.getProperty("excell.directory");
    } catch (IOException e) {
      e.printStackTrace();
    }
  }


  /**
   * 获取指定数据库下，表名为tablename的表的数据结构
   *
   * @author sosmcc
   * @paramtablename
   */
  public void getLibaryStructByTablename(String tablename) {
    LibaryReadTable libaryReadTable = new LibaryReadTable();
    try {

      //标题
      String[] title = {"名称", "代码", "数据类型", "长度"};
      //文件名
      String fileName = tablename + System.currentTimeMillis() + ".xls";
      //sheet名
      String sheetName = tablename;

      //该数据库里所有的表
      LibaryProperties libaryProperties = libaryReadTable.readlibary();

      //获取表
      List<ShowTableDto> tableList = DbTransToExcellUtils.getLibraryAllTableStruct(libaryProperties, tablename);

      //转换成【】【】
        String[][] content = DbTransToExcellUtils.setTableStructContent(tableList, title);

      HSSFWorkbook hssfWorkbook = ExcelPoiUtils.genericTemplate(sheetName, title, content, null);

      ExcelPoiUtils.outputExcell(hssfWorkbook, fileName, fileAddress);
    } catch (SQLException | IOException throwables) {
      throwables.printStackTrace();
    }
  }


  /**
   * 获取指定数据库下，所有表的数据结构
   *
   * @param
   * @author sosmcc
   */
  public void getExcellByLibary() {
    HSSFWorkbook wb = null;

    LibaryReadTable libaryReadTable = new LibaryReadTable();

    ArrayList<List<ShowTableDto>> library = new ArrayList<>();
    try {
      LibaryProperties libaryProperties = libaryReadTable.readlibary();

      //获取数据库表单
      for (String tablename : libaryProperties.keySet()) {
        library.add(DbTransToExcellUtils.getLibraryAllTableStruct(libaryProperties, tablename));
      }

      String[] title = {"名称", "代码", "数据类型", "长度"};
      //转换成string[][]
      for (List<ShowTableDto> showTableDtos : library) {
        String[][] content = DbTransToExcellUtils.setTableStructContent(showTableDtos, title);
        wb = ExcelPoiUtils.genericTemplate(showTableDtos.get(0).getTablename(), title, content, wb);
      }
      ExcelPoiUtils.outputExcell(wb, "数据库" + System.currentTimeMillis() + ".xls", fileAddress);

    } catch (SQLException | IOException sqlException) {
      sqlException.printStackTrace();
    }

  }

  /**
   * 得到自定义第表格样式
   *
   * @param dbTransToExcell 列名映射数据库字段的实体类集合
   * @param tablename       数据库的表名
   * @author C.c
   * @since 2022-08-16 15:59
   **/
  public void getCustomExcell(List<DBTransToExcell> dbTransToExcell, String tablename) throws SQLException, IOException {
    CustomReadTable customReadTable = new CustomReadTable();
    ResultSet readcloum = customReadTable.readcloum(dbTransToExcell, tablename);

    String[] title = DbTransToExcellUtils.setTitle(dbTransToExcell);
    String[][] content = DbTransToExcellUtils.setContent(title, readcloum);
    HSSFWorkbook hssfWorkbook = ExcelPoiUtils.genericTemplate(tablename, title, content, null);
    ExcelPoiUtils.outputExcell(hssfWorkbook, tablename + ".xls", fileAddress);

  }


}
